package per.lee.spingboothelper.db.dao;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import lombok.extern.java.Log;
import lombok.extern.slf4j.Slf4j;
import per.lee.spingboothelper.db.model.ShowSql;
import per.lee.spingboothelper.db.model.SlowSqlModel;
import per.lee.spingboothelper.utils.POIUtil;

public class BaseDao {

	static Map<String, String> TEST_DB = new HashMap<String, String>();
	
	static Map<String, String> DEV_DB = new HashMap<String, String>();
	
	static final String DEV_URL = "jdbc:mysql://drds2219q71k0f46.drds.aliyun.boc:3306/yz_dev_object_metrics?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
	static final String DEV_USER = "yz_dev_object_metrics";
	static final String DEV_PASS = "qsaMvgfiX5iq4dRm";

	static final String TEST_URL = "jdbc:mysql://drds2219q71k0f46.drds.aliyun.boc:3306/yz_test_object_metrics?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
	static final String TEST_USER = "yz_test_object_metrics";
	static final String TEST_PASS = "gPq3d8lZPF4bVdaO";
//
//	drds2219q71k0f46.drds.aliyun.boc/3306
//	yz_test_object_metrics/gPq3d8lZPF4bVdaO
	
	public static String ENV = "test";
	
	static {
		TEST_DB.put("url", TEST_URL);
		TEST_DB.put("user", TEST_USER);
		TEST_DB.put("password", TEST_PASS);
		
		DEV_DB.put("url", DEV_URL);
		DEV_DB.put("user", DEV_USER);
		DEV_DB.put("password", DEV_PASS);

	}
	public static Connection getConnection() throws SQLException {
		return getConnection(ENV);
	}
	public static Connection getConnection(String env) throws SQLException {
		Map<String, String> map = env.equals("dev") ? DEV_DB:TEST_DB;
		return DriverManager.getConnection(map.get("url"), map.get("user"), map.get("password"));
	}
	public static Statement createStatement() throws SQLException {
		return getConnection().createStatement();
	}
	
	public static ArrayList<Map<String, Object>> query(String sql) {
		try {
			Statement statement = createStatement();
			ResultSet resultSet = null;
			boolean returnFlag = statement.execute(sql);
			if (returnFlag) {
				resultSet = statement.getResultSet();
			} else {
				return new ArrayList<Map<String, Object>>();
			}

			// get metadata
			ResultSetMetaData metaData = null;
			metaData = resultSet.getMetaData();

			// get column names
			int colCount = metaData.getColumnCount();
			ArrayList<String> colList = new ArrayList<String>();
			for (int Index = 1; Index <= colCount; Index++) {
				colList.add(metaData.getColumnName(Index));
			}

			// fetch out rows
			ArrayList<Map<String, Object>> rowList = new ArrayList<Map<String, Object>>();

			while (resultSet.next()) {
				HashMap<String, Object> row = new HashMap<String, Object>();
				for (String Col_Name : colList) {
					Object Val = resultSet.getObject(Col_Name);
					row.put(Col_Name, Val);
				}
				rowList.add(row);
			}

			// close statement
			statement.close();
			// pass back rows
			return rowList;
		} catch (Exception e) {
			e.printStackTrace();
			System.err.print(e.getMessage());
			return new ArrayList<Map<String, Object>>();

		}

	}

}
